﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using TCMS.Models.Entities;
using MySql.Data.MySqlClient;
using TCMS.Models.DBFatory;

namespace TCMS.Models.DAO
{
	public class ShopFloorDAOImp : ShopFloorDAO
	{

		private MySqlConnection conn = null;
		public static ShopFloorEntity _shopFloor = null;
		public static List<ShopFloorEntity> _shopFloors = null;

		public string sql_getAllShopFloors = "select * from shopfloors;";

		public string sql_addNewShopFloor = @"insert into shopfloors ( id, shopName, status, Xposition, Yposition, remark) 
										VALUES(@1,@2,@3,@4,@5,@6)";

		public static readonly string SQL_UPDATE_SHOPFLOORS = @"INSERT INTO shopfloors (id, shopName, Xposition, Yposition, Icon, Status) 
                                        VALUES(@id, @shopName, @Xposition, @Yposition, @Icon, @Status)
                                        ON DUPLICATE KEY UPDATE shopName=@shopName, Xposition=@Xposition, YPosition=@Yposition, Icon=@Icon, Status=@Status";

		public static readonly string SQL_DELETE_SHOPFLOOR = @"DELETE FROM shopfloors WHERE id = @id";

        public const string SQL_MARKASUNUNAVAILABLE = @"UPDATE shopfloors SET status=@status WHERE id=@id";

		/// <summary>
		/// gets all shopfloors for the display on main pane.
		/// </summary>
		/// <returns></returns>
		public List<ShopFloorEntity> getShopFloors()
		{
			_shopFloors = new List<ShopFloorEntity>();
			conn = DBConnection.GetConnection();
			try
			{
				MySqlCommand cmd = new MySqlCommand(sql_getAllShopFloors, conn);
				MySqlDataReader rdr = cmd.ExecuteReader();

				while (rdr.Read())
				{
					_shopFloors.Add(new ShopFloorEntity { 
						ID = rdr[0].ToString(),
						ShopName = rdr[1].ToString(),
						Status = rdr.IsDBNull(2) ? 0 : rdr.GetInt32(2), 
						Xposition = rdr.IsDBNull(3) ? 0 : rdr.GetInt32(3), 
						Yposition = rdr.IsDBNull(4) ? 0: rdr.GetInt32(4), 
                        Icon = rdr[5].ToString(),
						Remark = rdr[6].ToString() 
					});
				}
			}
			catch (Exception ex)
			{ }
			conn.Close();
			return _shopFloors;
		}

		/// <summary>
		/// adds a new shopfloor into DB
		/// </summary>
		/// <param name="newShopFloor"></param>
		/// <returns></returns>
		public int addNewShopFloor(ShopFloorEntity newShopFloor)
		{
			int IsSuccess = 0;
			conn = DBConnection.GetConnection();
			try
			{
				MySqlCommand cmd = new MySqlCommand(sql_addNewShopFloor, conn);
				cmd.Parameters.AddWithValue("@1", newShopFloor.ID);
				cmd.Parameters.AddWithValue("@2", newShopFloor.ShopName);
				cmd.Parameters.AddWithValue("@3", newShopFloor.Status);
				cmd.Parameters.AddWithValue("@4", newShopFloor.Xposition);
				cmd.Parameters.AddWithValue("@5", newShopFloor.Yposition);
				cmd.Parameters.AddWithValue("@6", newShopFloor.Remark);

				IsSuccess = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{ }
			conn.Close();
			return IsSuccess;
		}


		public void UpdateShopFloors(IEnumerable<ShopFloorEntity> shopFloors)
		{
			conn = DBConnection.GetConnection();

			try
			{
				var tran = conn.BeginTransaction();

				try
				{
					foreach (var shopFloor in shopFloors)
					{
						MySqlCommand cmd = new MySqlCommand(SQL_UPDATE_SHOPFLOORS, conn, tran);
						cmd.Parameters.AddWithValue("@id", shopFloor.ID);
						cmd.Parameters.AddWithValue("@ShopName", shopFloor.ShopName);
						cmd.Parameters.AddWithValue("@Xposition", shopFloor.Xposition);
						cmd.Parameters.AddWithValue("@Yposition", shopFloor.Yposition);
                        cmd.Parameters.AddWithValue("@Icon", shopFloor.Icon);
                        cmd.Parameters.AddWithValue("@Status", shopFloor.Status);

						cmd.ExecuteNonQuery();

                        if (shopFloor.Status == 2)
                        {
                            MySqlCommand ucmd = new MySqlCommand(SQL_MARKASUNUNAVAILABLE, conn, tran);
                            ucmd.Parameters.AddWithValue("@id", shopFloor.ID);
                            ucmd.Parameters.AddWithValue("@status", 2);

                            ucmd.ExecuteNonQuery();
                        }
					}

					tran.Commit();
				}
				catch (Exception)
				{
					tran.Rollback();
					throw;
				}
			}
			finally
			{
				conn.Close();
			}
		}


		public void DeleteShopFloor(Guid id)
		{
			conn = DBConnection.GetConnection();

			try
			{
				var cmd = new MySqlCommand(SQL_DELETE_SHOPFLOOR, conn);
				cmd.Parameters.AddWithValue("@id", id);
				cmd.ExecuteNonQuery();
			}
			finally
			{
				conn.Close();
			}
		}
    }
}